﻿using Microsoft.EntityFrameworkCore;
using SQLitePCL;
using System;
using System.Collections.Generic;
using System.Linq;

namespace EFDemo
{
	class Program
	{
		static void Main(string[] args)
		{
			using (MyContext dc = new MyContext())
			{
				InitData(dc);

				var students = dc.Set<Student>().AsNoTracking().ToQueryString();
				Log(students, "AsNoTracking");

				var students2 = dc.Set<Student>().Include(x => x.StudentMajors).ThenInclude(x => x.Major).ToQueryString();
				Log(students2, "ThenInclude");

				var student3 = dc.Set<Student>().Select(x => new
				{
					name = x.Name,
					majors = string.Join(',', x.StudentMajors.Select(y => y.Major.MajorCode))
				}).ToQueryString();
				Log(student3, "many to one line");

				var student4 = dc.Set<Student>().Where(x => x.StudentMajors.Select(y => y.Major.School.SchoolName).Contains("School1")).ToQueryString();
				Log(student4, "where contain");

				var student5 = dc.Set<Student>().GroupBy(x => x.IsValid).Select(x => new
				{
					name = x.Key,
					count = x.Count()
				}).ToQueryString();
				Log(student5, "GroupBy");

				var student6 = dc.Set<Student>().GroupBy(x => new { x.IsValid, x.EnRollDate }).Where(x => x.Average(a => a.Age) > 15).Select(x => new
				{
					name = x.Key.IsValid,
					date = x.Key.EnRollDate,
					count = x.Count(),
					aveAge = x.Average(a=>a.Age)
				}).ToQueryString();
				Log(student6, "GroupBy Where");

				//*****SelectMany******
				var student7 = dc.Set<Student>().SelectMany(x => x.StudentMajors, (a, b) => new
				{
					stu = a,
					school = b.Major.School.SchoolName
				}).GroupBy(x => new { x.school, x.stu.EnRollDate }, x => x.stu.Age).Where(x => x.Average() > 15).Select(x => new
				{
					school = x.Key.school,
					date = x.Key.EnRollDate,
					count = x.Count(),
					aveAge = x.Average()
				}).ToQueryString();
				Log(student7, "SelectMany");

				var studentsNoKey = dc.Set<Student2>().ToQueryString();
				Log(studentsNoKey, "studentsNoKey");

				var studentsNoKey2 = dc.Set<Student2>().Include(x => x.StudentMajors).ThenInclude(x => x.Major).ToQueryString(); //不能使用Include
				Log(studentsNoKey2, "studentsNoKey2");

				var studentsNoKey3 = dc.Set<Student2>().Select(x => new
				{
					name = x.Name,
					majors = string.Join(',', dc.Set<StudentMajor2>().Where(y => y.StudentName == x.Name).Join(dc.Set<Major2>(), y => y.MajorName, y => y.MajorName, (middle, major) => new { mid = middle, maj = major }).Select(y => y.maj.MajorCode))
				}).ToList();

				var studentsNoKey4 = dc.Set<Student2>().Where(x =>
							dc.Set<StudentMajor2>().Where(y => y.StudentName == x.Name)
							.Join(dc.Set<Major2>(), y => y.MajorName, y => y.MajorName, (middle, major) => new { mid = middle, maj = major })
							.Join(dc.Set<School2>(), y => y.maj.SchoolName, y => y.SchoolName, (last, school) => new { last = last, school = school })
							.Select(y => y.school.SchoolName).Contains("School1")
			   ).ToList();

				var studentsNoKey5 = dc.Set<Student2>().GroupBy(x => x.IsValid).Select(x => new
				{
					name = x.Key,
					count = x.Count()
				}).ToList();

				var studentsNoKey6 = dc.Set<Student2>().GroupBy(x => new { x.IsValid, x.EnRollDate }, x => x.Age).Where(x => x.Average() > 15).Select(x => new
				{
					name = x.Key.IsValid,
					date = x.Key.EnRollDate,
					count = x.Count(),
					aveAge = x.Average()
				}).ToList();

				var studentsNoKey7 = dc.Set<Student2>().SelectMany(x =>
									dc.Set<StudentMajor2>().Where(y => y.StudentName == x.Name).Join(dc.Set<Major2>(), y => y.MajorName, y => y.MajorName, (middle, major) => new { mid = middle, maj = major })
									.Join(dc.Set<School2>(), y => y.maj.SchoolName, y => y.SchoolName, (last, school) => new { last = last, school = school })
									.Select(y => y.school.SchoolName),
							(a, b) => new
							{
								stu = a,
								school = b
							}).GroupBy(x => new { x.school, x.stu.EnRollDate }, x => x.stu.Age).Where(x => x.Average() > 15).Select(x => new
							{
								school = x.Key.school,
								date = x.Key.EnRollDate,
								count = x.Count(),
								aveAge = x.Average()
							}).ToList();

			}
			Console.WriteLine("Hello World!");
		}

		static void Log(string sql, string title)
		{
			Console.WriteLine("Title: " + title);
			Console.WriteLine(sql);
			Console.WriteLine();
		}
		static void InitData(MyContext dc)
		{
			//如果是第一次建库
			if (dc.Database.EnsureCreated() == true)
			{
				//初始化有主外键关系的表
				List<School> schools = new List<School>();
				for (int i = 1; i <= 10; i++)
				{
					School s = new School
					{
						SchoolCode = "s00" + i,
						SchoolName = "School" + i,
						Majors = new List<Major>()
					};
					for (int j = 1; j <= 5; j++)
					{
						Major m = new Major
						{
							MajorCode = "m00" + j,
							MajorName = $"School{i}_Major{j}"
						};
						s.Majors.Add(m);
					}
					schools.Add(s);
				}
				Random r = new Random();
				List<Student> students = new List<Student>();
				for (int i = 1; i <= 100; i++)
				{
					Student stu = new Student
					{
						Name = "Student" + i,
						IsValid = i % 3 == 0,
						EnRollDate = DateTime.Today.AddDays(i % 5),
						Age = r.Next(10, 20)
					};
					students.Add(stu);
				}

				List<StudentMajor> middle = new List<StudentMajor>();

				for (int i = 1; i <= 1000; i++)
				{
					StudentMajor m = new StudentMajor
					{
						Major = schools[r.Next(10)].Majors[r.Next(5)],
						Student = students[r.Next(100)]
					};
					middle.Add(m);
				}
				dc.Schools.AddRange(schools);
				dc.Students.AddRange(students);
				dc.Set<StudentMajor>().AddRange(middle);
				dc.SaveChanges();

				//初始化没有主外键关联的表
				List<School2> schools2 = new List<School2>();
				List<Major2> majors2 = new List<Major2>();
				for (int i = 1; i <= 10; i++)
				{
					School2 s = new School2
					{
						SchoolCode = "s00" + i,
						SchoolName = "School" + i
					};
					for (int j = 1; j <= 5; j++)
					{
						Major2 m = new Major2
						{
							MajorCode = "m00" + j,
							MajorName = $"School{i}_Major{j}",
							SchoolName = s.SchoolName
						};
						majors2.Add(m);
					}
					schools2.Add(s);
				}
				List<Student2> students2 = new List<Student2>();
				for (int i = 1; i <= 100; i++)
				{
					Student2 stu = new Student2
					{
						Name = "Student" + i,
						IsValid = i % 3 == 0,
						EnRollDate = DateTime.Today.AddDays(i % 5),
						Age = r.Next(10, 20)
					};
					students2.Add(stu);
				}

				List<StudentMajor2> middle2 = new List<StudentMajor2>();

				for (int i = 1; i <= 1000; i++)
				{
					StudentMajor2 m = new StudentMajor2
					{
						MajorName = majors2[r.Next(50)].MajorName,
						StudentName = students2[r.Next(100)].Name
					};
					middle2.Add(m);
				}
				dc.SchoolsNoFK.AddRange(schools2);
				dc.MajorsNoFK.AddRange(majors2);
				dc.StudentsNoFK.AddRange(students2);
				dc.Set<StudentMajor2>().AddRange(middle2);
				dc.SaveChanges();

			}
		}

	}
}
